package org.light.framework.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.light.framework.dao.DefaultDataSourceFactory;
import org.light.framework.orm.DataRow;
import org.light.framework.orm.DataTable;
import org.light.framework.util.ConfigUtil;
import org.light.framework.util.DateHelper;
import org.light.framework.util.DateStyle;
import org.light.framework.util.StringUtil;

/**
 * 数据库操作工具类
 * 
 * @author de.cel
 *
 */
public class SQLHelper {

//	private static final String DRIVER;
//	private static final String URL;
//	private static final String USERNAME;
//	private static final String PASSWORD;

	
	private static DataSource dataSourceFactory = null;
	private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<Connection>();

	static {

//		DRIVER = ConfigUtil.getJdbcDriver();
//		URL = ConfigUtil.getJdbcUrl();
//		USERNAME = ConfigUtil.getJdbcUsername();
//		PASSWORD = ConfigUtil.getJdbcPassword();

		dataSourceFactory=new DefaultDataSourceFactory().getDataSource();
		
//		try {
//			Class.forName(DRIVER).newInstance();
//		} catch (Exception ex) {
//			System.out.println(" jdbc 驱动加载失败，" + ex);
//			ex.printStackTrace();
//		}
	}

	public static Connection getConnection() {
		Connection connection = CONNECTION_HOLDER.get();
		if (connection == null) {
			try {
				
				//直接创建
				//connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
				
				//从DBCP连接池中获取
				connection=dataSourceFactory.getConnection();
			} catch (Exception ex) {
				System.out.println("驱动加载失败");
				ex.printStackTrace();
			} finally {
				CONNECTION_HOLDER.set(connection);
			}
		}
		return connection;
	}

	public static void closeConnection() {
		Connection connection = CONNECTION_HOLDER.get();
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO: handle exception
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}

	/**
	 * 查询结果集
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet executeResultSet(String sql) throws SQLException {

		ResultSet rs = null;
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			return preparedStatement.executeQuery();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}

	}

	/**
	 * 用于带参数的查询，返回结果集
	 * 
	 * @param sql
	 * @param paramters
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("rawtypes")
	public static List queryList(String sql, Object... paramters) throws SQLException {

		ResultSet rs = null;
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
			}
			rs = preparedStatement.executeQuery();
			return ResultToListMap(rs);
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 用于带参数的查询，返回结果集
	 * 
	 * @param sql
	 * @param paramters
	 * @return
	 * @throws Exception
	 */
	public static DataTable fillDataTable(String sql, Object... paramters) throws Exception {

		ResultSet rs = null;
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
			}
			rs = preparedStatement.executeQuery();
			return ResultSetToDataTable(rs);
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 返回单个结果的值，如count\min\max等等
	 * 
	 * @param sql语句
	 * @return 结果集
	 * @throws SQLException
	 */
	public static Object executeScalar(String sql) throws SQLException {
		Object result = null;
		ResultSet rs = null;
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			rs = preparedStatement.executeQuery();
			if (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}

	}

	/**
	 * 返回单个结果值，如count\min\max等
	 * 
	 * @param sql
	 *            语句
	 * @param paramters
	 *            参数列表
	 * @return 结果
	 * @throws SQLException
	 */
	public static Object executeScalar(String sql, Object... paramters) throws SQLException {
		Object result = null;
		ResultSet rs = null;
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
			}
			rs = preparedStatement.executeQuery();
			if (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 用于增删改
	 * 
	 * @param sql
	 *            SQL语句
	 * @return 影响行数
	 * @throws SQLException
	 */
	public static int executeNonQuery(String sql) throws SQLException {

		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}
	}

	/**
	 * 用于增删改（带参数）
	 * 
	 * @param sql
	 *            SQL语句
	 * @param paramters
	 *            查询参数
	 * @return 影响行数
	 * @throws SQLException
	 */
	public static int executeNonQuery(String sql, Object... paramters) throws SQLException {
		try {
			PreparedStatement preparedStatement=getPreparedStatement(sql);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}
	}

	/**
	 * 插入值后返回主键值
	 * 
	 * @param sql
	 *            插入SQL语句
	 * @return 返回结果
	 * @throws Exception
	 */
	public static Object insertWithReturnPrimeKey(String sql) throws SQLException {

		Object result = null;
		try {
			Connection conn = getConnection(); // JdbcUnits.getConnection();
			PreparedStatement	preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}

	}

	/**
	 * 插入值后返回主键值
	 * 
	 * @param sql
	 *            插入SQL语句
	 * @param paramters
	 *            参数列表
	 * @return 返回结果
	 * @throws SQLException
	 */
	public static Object insertWithReturnPrimeKey(String sql, Object... paramters) throws SQLException {

		Object result = null;
		try {
			Connection conn = getConnection();
			PreparedStatement preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < paramters.length; i++) {
				preparedStatement.setObject(i + 1, paramters[i]);
			}
			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		}

	}

	/**
	 * 调用存储过程执行查询
	 * 
	 * @param procedureSql
	 *            存储过程
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("rawtypes")
	public static List executeSP(String procedureSql) throws SQLException {
		ResultSet rs = null;
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			rs = callableStatement.executeQuery();
			return ResultToListMap(rs);
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 调用存储过程（带参数）,执行查询
	 * 
	 * @param procedureSql
	 *            存储过程
	 * @param paramters
	 *            参数表
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("rawtypes")
	public static List executeSP(String procedureSql, Object... paramters) throws SQLException {
		ResultSet rs = null;
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			for (int i = 0; i < paramters.length; i++) {
				callableStatement.setObject(i + 1, paramters[i]);
			}
			rs = callableStatement.executeQuery();
			return ResultToListMap(rs);
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 调用存储过程，查询单个值
	 * 
	 * @param procedureSql
	 * @return
	 * @throws SQLException
	 */
	public static Object executeSPScalar(String procedureSql) throws SQLException {
		Object result = null;
		ResultSet rs = null;
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			rs = callableStatement.executeQuery();
			while (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	/**
	 * 调用存储过程(带参数)，查询单个值
	 * 
	 * @param procedureSql
	 * @param parameters
	 * @return
	 * @throws SQLException
	 */
	public static Object executeSPScalar(String procedureSql, Object... paramters) throws SQLException {
		Object result = null;
		ResultSet rs = null;
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			for (int i = 0; i < paramters.length; i++) {
				callableStatement.setObject(i + 1, paramters[i]);
			}
			rs = callableStatement.executeQuery();
			while (rs.next()) {
				result = rs.getObject(1);
			}
			return result;
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free(rs);
		}
	}

	public static Object executeSPScalarOutput(String procedureSql) throws SQLException {
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			callableStatement.registerOutParameter(0, Types.OTHER);
			callableStatement.execute();
			return callableStatement.getObject(0);

		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}

	}

	/**
	 * 调用存储过程，执行增删改
	 * 
	 * @param procedureSql
	 *            存储过程
	 * @return 影响行数
	 * @throws SQLException
	 */
	public static int callableUpdate(String procedureSql) throws SQLException {
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			return callableStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}
	}

	/**
	 * 调用存储过程（带参数），执行增删改
	 * 
	 * @param procedureSql
	 *            存储过程
	 * @param parameters
	 * @return 影响行数
	 * @throws SQLException
	 */
	public static int callableUpdate(String procedureSql, Object... parameters) throws SQLException {
		try {
			CallableStatement callableStatement=getCallableStatement(procedureSql);
			for (int i = 0; i < parameters.length; i++) {
				callableStatement.setObject(i + 1, parameters[i]);
			}
			return callableStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			free();
		}
	}

	/**
	 * 批量更新数据
	 * 
	 * @param sqlList
	 *            一组SQL
	 * @return
	 */
	public static int[] batchUpdate(List<String> sqlList) {

		int[] result = new int[] {};
		Statement statenent = null;
		Connection conn = null;

		try {
			// conn =JdbcUnits.getConnection();
			conn = getConnection();
			conn.setAutoCommit(false);
			statenent = conn.createStatement();
			for (String sql : sqlList) {
				statenent.addBatch(sql);
			}
			result = statenent.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				throw new ExceptionInInitializerError(e1);
			}
			throw new ExceptionInInitializerError(e);
		} finally {
			free(statenent, null);
		}
		return result;
	}

	public static DbType GetDbType() {

		String driverName = ConfigUtil.getJdbcDriver().toLowerCase();
		if (driverName.contains("mysql"))
			return DbType.MySql;
		else if (driverName.contains("sqlserver")) {
			return DbType.SqlServer;
		} else if (driverName.contains("oracle")) {
			return DbType.Oracle;
		}
		return DbType.MySql;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	private static List ResultToListMap(ResultSet rs) throws SQLException {
		List list = new ArrayList();
		while (rs.next()) {
			ResultSetMetaData md = rs.getMetaData();
			Map map = new HashMap();
			for (int i = 1; i < md.getColumnCount(); i++) {
				map.put(md.getColumnLabel(i), rs.getObject(i));
			}
			list.add(map);
		}
		return list;
	}

	private static DataTable ResultSetToDataTable(ResultSet resultSet) throws SQLException, Exception {

		DataTable dt = new DataTable();
		while (resultSet.next()) {
			ResultSetMetaData md = resultSet.getMetaData();
			DataRow dr = dt.newRow();

			for (int i = 1; i <= md.getColumnCount(); i++) {

				dr.setValue(md.getColumnLabel(i), castDataType(resultSet.getObject(i)));
			}
			dt.addRow(dr);
		}
		return dt;
	}

	private static Object castDataType(Object obj) {

		if (obj == null)
			return obj;
		if (obj.getClass().getSimpleName().equals("BigDecimal"))
			obj = StringUtil.toDouble(obj);
		else if (obj.getClass().getSimpleName().equals("Timestamp"))
			obj = DateHelper.dateToString(DateHelper.stringToDate(obj.toString()), DateStyle.YYYY_MM_DD_HH_MM_SS);

		return obj;
	}

	/**
	 * 获取PreparedStatement
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	private static PreparedStatement getPreparedStatement(String sql) throws SQLException {
		Connection conn = getConnection();
		return conn.prepareStatement(sql);
	}

	/**
	 * 获取CallableStatement
	 * 
	 * @param procedureSql
	 * @throws SQLException
	 */
	private static CallableStatement getCallableStatement(String procedureSql) throws SQLException {
		// conn = JdbcUnits.getConnection();
		Connection conn = getConnection();
		return conn.prepareCall(procedureSql);
	}

	/**
	 * 释放资源
	 * 
	 * @param rs
	 *            结果集
	 */
	public static void free(ResultSet rs) {

		try {
			if (rs != null)
				rs.close();
			closeConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 释放资源
	 * 
	 * @param statement
	 * @param rs
	 */
	public static void free(Statement statement, ResultSet rs) {
		try {
			statement.close();
			rs.close();
			closeConnection();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 释放资源
	 */
	public static void free() {
		closeConnection();
	}

	public static void beginTransaction() {

		Connection conn = getConnection();
		if (conn != null) {
			try {
				conn.setAutoCommit(false);
			} catch (SQLException e) {
				System.out.println("开启事务出错！" + e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(conn);
			}
		}

	}

	public static void commitTransaction() {
		
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.commit();
                conn.close();
            } catch (SQLException e) {
                System.out.println("提交事务出错！" + e);
                throw new RuntimeException(e);
            } finally {
            	CONNECTION_HOLDER.remove();
            }
        }

	}

	public static void rollbackTransaction() {
		
        Connection conn = getConnection();
        if (conn != null) {
            try {
                conn.rollback();
                conn.close();
            } catch (SQLException e) {
                System.out.println("回滚事务出错！" + e);
                throw new RuntimeException(e);
            } finally {
            	CONNECTION_HOLDER.remove();
            }
        }
	}

}
